package cn.jly.spring.jdbc.dao;

import cn.jly.spring.jdbc.bean.Student;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * @author lanyangji
 * @description
 * @date 2020/11/11 上午 10:44
 */
@Repository
public class StudentDaoImpl implements StudentDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public int[] batchUpdate(List<Student> students) {
        return jdbcTemplate.batchUpdate("update student set `name` = ?, gender = ?, grade = ? where id = ?", new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                final Student student = students.get(i);
                ps.setString(1, student.getName());
                ps.setInt(2, student.getGender());
                ps.setInt(3, student.getGrade());
                ps.setInt(4, student.getId());
            }

            @Override
            public int getBatchSize() {
                return students.size();
            }
        });
    }

    @Override
    public int[] batchDelete(List<Integer> ids) {
        return jdbcTemplate.batchUpdate("delete from student where id = ?", new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                final Integer id = ids.get(i);
                ps.setInt(1, id);
            }

            @Override
            public int getBatchSize() {
                return ids.size();
            }
        });
    }

    @Override
    public int[] batchInsert(List<Student> students) {
        return jdbcTemplate.batchUpdate(
                "insert into student(`name`, gender, grade) value (?,?,?)",
                new BatchPreparedStatementSetter() {
                    @Override
                    public void setValues(PreparedStatement ps, int i) throws SQLException {
                        final Student student = students.get(i);
                        ps.setString(1, student.getName());
                        ps.setInt(2, student.getGender());
                        ps.setInt(3, student.getGrade());
                    }

                    @Override
                    public int getBatchSize() {
                        return students.size();
                    }
                }
        );
    }

    @Override
    public List<Student> getAll() {
        return jdbcTemplate.query("select * from student", new RowMapper<Student>() {
            @Override
            public Student mapRow(ResultSet resultSet, int i) throws SQLException {
                final Student student = new Student();
                student.setId(resultSet.getInt("id"));
                student.setName(resultSet.getString("name"));
                student.setGender(resultSet.getInt("gender"));
                student.setGrade(resultSet.getInt("grade"));
                return student;
            }
        });
    }

    @Override
    public Student getById(Integer id) {
        return jdbcTemplate.queryForObject(
                "select * from student where id = ?",
                new Object[]{id},
                new RowMapper<Student>() {
                    @Override
                    public Student mapRow(ResultSet rs, int rowNum) throws SQLException {
                        final Student student = new Student();
                        student.setId(rs.getInt("id"));
                        student.setName(rs.getString("name"));
                        student.setGender(rs.getInt("gender"));
                        student.setGrade(rs.getInt("grade"));

                        return student;
                    }
                });
    }

    @Override
    public int insert(Student student) {
        return jdbcTemplate.update("insert student(name, gender, grade) value (?,?,?)", new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement preparedStatement) throws SQLException {
                preparedStatement.setString(1, student.getName());
                preparedStatement.setInt(2, student.getGender());
                preparedStatement.setInt(3, student.getGrade());
            }
        });
    }

    @Override
    public int deleteById(Integer id) {
        return jdbcTemplate.update("delete from student where id = ?", new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement preparedStatement) throws SQLException {
                preparedStatement.setInt(1, id);
            }
        });
    }

    @Override
    public int updateById(Student student) {
        if (student.getId() == null) {
            return 0;
        }

        return jdbcTemplate.update("update student set `name` = ?, gender = ?, grade = ? where id = ?", new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement preparedStatement) throws SQLException {
                preparedStatement.setString(1, student.getName());
                preparedStatement.setInt(2, student.getGender());
                preparedStatement.setInt(3, student.getGrade());
                preparedStatement.setInt(4, student.getId());
            }
        });
    }
}
